package com.ylx.excel.service.impl;

import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.ylx.excel.bo.ImportAppBo;
import com.ylx.excel.excel.model.AppInfoImport;
import com.ylx.excel.excel.model.ImportData;
import com.ylx.excel.model.AcApInfo;
import com.ylx.excel.model.AcAppInfo;
import com.ylx.excel.model.AcAppSimAuth;
import com.ylx.excel.model.AcAppSimShield;
import com.ylx.excel.service.ExcelService;
import com.ylx.core.utils.AESUtil;
import com.ylx.excel.utils.ExcelUtils;
import com.ylx.mybatis.utils.SqlCreateUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * excel相关
 * @author yanglixin
 */
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {

    public static final String AES_KEY = "D4A5969B97028B32AEA03167952A8E39";

    @Override
    public String importSim(ImportAppBo bo) {
        //读取
        List<AppInfoImport> importData = ExcelUtils.importByExcel(bo.getFile(), AppInfoImport.class);
        //生产
        Map<String, AcApInfo> apInfoMap = new HashMap<>();

        //保存每个ap当前前的app数量
        Map<String, Integer> keyApIdValueAppNum = new HashMap<>();
        //中移
        keyApIdValueAppNum.put("91440106MA59AKEH1Y",2);
        StringBuilder sql = new StringBuilder();
        int apNumber = bo.getApNumber();
        for (AppInfoImport data : importData) {
            //加密
            String creditCode = null;
            String apName = null;
            String contact = null;
            String contactPhone = null;
            String contactEmail = null;
            try {
                apName = AESUtil.aesEncrypt(data.getApName(), AES_KEY);
                if (CharSequenceUtil.isNotBlank(data.getCreditCode())) {
                    creditCode = AESUtil.aesEncrypt(data.getCreditCode(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContact())) {
                    contact = AESUtil.aesEncrypt(data.getContact(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContactPhone())) {
                    contactPhone = AESUtil.aesEncrypt(data.getContactPhone(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContactEmail())) {
                    contactEmail = AESUtil.aesEncrypt(data.getContactEmail(), AES_KEY);
                }
            } catch (Exception e) {
                log.info("加密失败");
            }

            //ap信息
            String apId;
            //根据sim盾的的社会信用码去重
            AcApInfo acApInfo = apInfoMap.get(data.getCreditCode());
            if (Objects.isNull(acApInfo)) {
                if (!CharSequenceUtil.equals(data.getCreditCode(),"91440106MA59AKEH1Y")) {
                    //为空新增  且不为中移
                    apNumber++;
                    apId = String.format("A%s",StrUtil.fill(String.valueOf(apNumber), '0', 4, true));
                    //当前ap还未存在 新增
                    acApInfo = new AcApInfo();
                    acApInfo.setId(apId);
                    acApInfo.setName(apName);
                    acApInfo.setCreditCode(creditCode);
                    acApInfo.setDeleteFlag(0);
                    acApInfo.setIfSimShieldUseDefaultPrompt(0);
                    //生成ap的sql
                    sql.append(SqlCreateUtil.generateInsertSql(acApInfo));

                    apInfoMap.put(data.getCreditCode(),acApInfo);
                    keyApIdValueAppNum.put(data.getCreditCode(),1);
                } else {
                    apId = "A0006";
                    Integer appNum = keyApIdValueAppNum.get(data.getCreditCode());
                    keyApIdValueAppNum.put(data.getCreditCode(),appNum + 1);
                }
            } else {
                //当前ap已存在  取出apId并将app数量加一
                apId = acApInfo.getId();

                Integer appNum = keyApIdValueAppNum.get(data.getCreditCode());
                keyApIdValueAppNum.put(data.getCreditCode(),appNum + 1);
            }

            //app信息
            String appId = apId + StrUtil.fill(String.valueOf(keyApIdValueAppNum.get(data.getCreditCode())), '0', 3, true);

            AcAppInfo acAppInfo = new AcAppInfo();
            acAppInfo.setAppId(appId);
            acAppInfo.setAppName(data.getAppName());
            acAppInfo.setApId(apId);
            acAppInfo.setContact(contact);
            acAppInfo.setContactPhone(contactPhone);
            acAppInfo.setContactEmail(contactEmail);

            acAppInfo.setStatus(0);
            acAppInfo.setNote("V2.2.5新增sim盾业务方");
            acAppInfo.setDeleteFlag(0);
            acAppInfo.setIfCloseLicense(1);
            acAppInfo.setSource(1);

            //生成app的sql
            sql.append(SqlCreateUtil.generateInsertSql(acAppInfo));

            if (CharSequenceUtil.isNotBlank(data.getAppId())) {
                //关联表sim认证
                AcAppSimAuth acAppSimAuth = new AcAppSimAuth();
                acAppSimAuth.setAppId(appId);
                acAppSimAuth.setSimAuthApId(data.getApId());
                acAppSimAuth.setSimAuthAppId(data.getAppId());
                //sim认证
                sql.append(SqlCreateUtil.generateInsertSql(acAppSimAuth));
            } else {
                //关联表sim盾
                AcAppSimShield acAppSimShield = new AcAppSimShield();
                acAppSimShield.setAppId(appId);
                acAppSimShield.setSimShieldApId(data.getApId());
                //sim盾
                sql.append(SqlCreateUtil.generateInsertSql(acAppSimShield));
            }
        }

        return sql.toString();
    }

    @Override
    public String importSimAuthAdd(ImportAppBo bo, HttpServletResponse response) {
        //读取
        List<AppInfoImport> importData = ExcelUtils.importByExcel(bo.getFile(), AppInfoImport.class);

        //数据分类
        List<AppInfoImport> needNew = importData.stream().filter(data -> Objects.equals(1, data.getNeedAdd())).collect(Collectors.toList());
        List<AppInfoImport> oldDataList = importData.stream().filter(data -> Objects.equals(0, data.getNeedAdd())).collect(Collectors.toList());

        //保存每个ap当前前的app数量
        Map<String, Integer> keyApIdValueAppNum = new HashMap<>();
        //保存已经新增的ap
        Map<String, AcApInfo> apInfoMap = new HashMap<>();

        Map<String, List<AppInfoImport>> oldGroupByApId = oldDataList.stream().collect(Collectors.groupingBy(AppInfoImport::getApId));
        for (String apId : oldGroupByApId.keySet()) {
            List<AppInfoImport> appInfoImports = oldGroupByApId.get(apId);
            keyApIdValueAppNum.put(apId, appInfoImports.size());
            AcApInfo apInfo = new AcApInfo();
            apInfo.setId(appInfoImports.get(0).getAuthApId());
            apInfoMap.put(apId,apInfo);
        }


        StringBuilder sql = new StringBuilder();
        int apNumber = bo.getApNumber();
        for (AppInfoImport data : needNew) {
            //加密
            String creditCode = null;
            String apName = null;
            String contact = null;
            String contactPhone = null;
            String contactEmail = null;
            try {
                apName = AESUtil.aesEncrypt(data.getApName(), AES_KEY);
                if (CharSequenceUtil.isNotBlank(data.getCreditCode())) {
                    creditCode = AESUtil.aesEncrypt(data.getCreditCode(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContact())) {
                    contact = AESUtil.aesEncrypt(data.getContact(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContactPhone())) {
                    contactPhone = AESUtil.aesEncrypt(data.getContactPhone(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContactEmail())) {
                    contactEmail = AESUtil.aesEncrypt(data.getContactEmail(), AES_KEY);
                }
            } catch (Exception e) {
                log.info("加密失败");
            }

            //ap信息
            String apId;
            //根据sim认证的apId去重
            AcApInfo acApInfo = apInfoMap.get(data.getApId());
            if (Objects.isNull(acApInfo)) {
                apNumber++;
                apId = String.format("A%s",StrUtil.fill(String.valueOf(apNumber), '0', 4, true));
                //当前ap还未存在 新增
                acApInfo = new AcApInfo();
                acApInfo.setId(apId);
                acApInfo.setName(apName);
                acApInfo.setCreditCode(creditCode);
                acApInfo.setDeleteFlag(0);
                acApInfo.setIfSimShieldUseDefaultPrompt(0);
                //生成ap的sql
                sql.append(SqlCreateUtil.generateInsertSql(acApInfo));

                apInfoMap.put(data.getApId(),acApInfo);
                keyApIdValueAppNum.put(data.getApId(),1);
            } else {
                //当前ap已存在  取出apId并将app数量加一
                apId = acApInfo.getId();

                Integer appNum = keyApIdValueAppNum.get(data.getApId());
                keyApIdValueAppNum.put(data.getApId(),appNum + 1);
            }

            //app信息
            String appId = apId + StrUtil.fill(String.valueOf(keyApIdValueAppNum.get(data.getApId())), '0', 3, true);

            AcAppInfo acAppInfo = new AcAppInfo();
            acAppInfo.setAppId(appId);
            acAppInfo.setAppName(data.getAppName());
            acAppInfo.setApId(apId);
            acAppInfo.setContact(contact);
            acAppInfo.setContactPhone(contactPhone);
            acAppInfo.setContactEmail(contactEmail);

            acAppInfo.setStatus(0);
            acAppInfo.setNote("V2.2.5sim认证后补数据");
            acAppInfo.setDeleteFlag(0);
            acAppInfo.setIfCloseLicense(1);
            //来源为sim认证
            acAppInfo.setSource(3);

            //生成app的sql
            sql.append(SqlCreateUtil.generateInsertSql(acAppInfo));
            //回填数据
            data.setAuthApId(acAppInfo.getApId());
            data.setAuthAppId(acAppInfo.getAppId());

            if (CharSequenceUtil.isNotBlank(data.getAppId())) {
                //关联表sim认证
                AcAppSimAuth acAppSimAuth = new AcAppSimAuth();
                acAppSimAuth.setAppId(appId);
                acAppSimAuth.setSimAuthApId(data.getApId());
                acAppSimAuth.setSimAuthAppId(data.getAppId());
                //sim认证
                sql.append(SqlCreateUtil.generateInsertSql(acAppSimAuth));
            } else {
                //关联表sim盾
                AcAppSimShield acAppSimShield = new AcAppSimShield();
                acAppSimShield.setAppId(appId);
                acAppSimShield.setSimShieldApId(data.getApId());
                //sim盾
                sql.append(SqlCreateUtil.generateInsertSql(acAppSimShield));
            }
        }
        //生成Excel回填数据
        ExcelUtils.exportLocal("C:\\Users\\1\\Desktop\\fileNew.xlsx",AppInfoImport.class,importData);

        return sql.toString();
    }

    @Override
    public String importSimAuth(ImportAppBo bo) {
        //读取
        List<AppInfoImport> importData = ExcelUtils.importByExcel(bo.getFile(), AppInfoImport.class);

        Map<String, AcApInfo> apInfoMap = new HashMap<>();
        //保存每个ap当前前的app数量
        Map<String, Integer> keyApIdValueAppNum = new HashMap<>();
        StringBuilder sql = new StringBuilder();
        int apNumber = bo.getApNumber();
        for (AppInfoImport data : importData) {
            //加密
            String creditCode = null;
            String apName = null;
            String contact = null;
            String contactPhone = null;
            String contactEmail = null;
            try {
                apName = AESUtil.aesEncrypt(data.getApName(), AES_KEY);
                if (CharSequenceUtil.isNotBlank(data.getCreditCode())) {
                    creditCode = AESUtil.aesEncrypt(data.getCreditCode(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContact())) {
                    contact = AESUtil.aesEncrypt(data.getContact(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContactPhone())) {
                    contactPhone = AESUtil.aesEncrypt(data.getContactPhone(), AES_KEY);
                }
                if (CharSequenceUtil.isNotBlank(data.getContactEmail())) {
                    contactEmail = AESUtil.aesEncrypt(data.getContactEmail(), AES_KEY);
                }
            } catch (Exception e) {
                log.info("加密失败");
            }

            //ap信息
            String apId;
            //根据sim认证的apId去重
            AcApInfo acApInfo = apInfoMap.get(data.getApId());
            if (Objects.isNull(acApInfo)) {
                apNumber++;
                apId = String.format("A%s",StrUtil.fill(String.valueOf(apNumber), '0', 4, true));
                //当前ap还未存在 新增
                acApInfo = new AcApInfo();
                acApInfo.setId(apId);
                acApInfo.setName(apName);
                acApInfo.setCreditCode(creditCode);
                acApInfo.setDeleteFlag(0);
                acApInfo.setIfSimShieldUseDefaultPrompt(0);
                //生成ap的sql
                sql.append(SqlCreateUtil.generateInsertSql(acApInfo));

                apInfoMap.put(data.getApId(),acApInfo);
                keyApIdValueAppNum.put(apId,1);
            } else {
                //当前ap已存在  取出apId并将app数量加一
                apId = acApInfo.getId();

                Integer appNum = keyApIdValueAppNum.get(apId);
                keyApIdValueAppNum.put(apId,appNum + 1);
            }

            //app信息
            String appId = apId + StrUtil.fill(String.valueOf(keyApIdValueAppNum.get(apId)), '0', 3, true);

            AcAppInfo acAppInfo = new AcAppInfo();
            acAppInfo.setAppId(appId);
            acAppInfo.setAppName(data.getAppName());
            acAppInfo.setApId(apId);
            acAppInfo.setContact(contact);
            acAppInfo.setContactPhone(contactPhone);
            acAppInfo.setContactEmail(contactEmail);

            acAppInfo.setStatus(0);
            acAppInfo.setNote("V2.2.5批量新增");
            acAppInfo.setDeleteFlag(0);
            acAppInfo.setIfCloseLicense(1);
            //来源为sim认证
            acAppInfo.setSource(3);

            //生成app的sql
            sql.append(SqlCreateUtil.generateInsertSql(acAppInfo));

            if (CharSequenceUtil.isNotBlank(data.getAppId())) {
                //关联表sim认证
                AcAppSimAuth acAppSimAuth = new AcAppSimAuth();
                acAppSimAuth.setAppId(appId);
                acAppSimAuth.setSimAuthApId(data.getApId());
                acAppSimAuth.setSimAuthAppId(data.getAppId());
                //sim认证
                sql.append(SqlCreateUtil.generateInsertSql(acAppSimAuth));
            } else {
                //关联表sim盾
                AcAppSimShield acAppSimShield = new AcAppSimShield();
                acAppSimShield.setAppId(appId);
                acAppSimShield.setSimShieldApId(data.getApId());
                //sim盾
                sql.append(SqlCreateUtil.generateInsertSql(acAppSimShield));
            }
        }

        return sql.toString();
    }

    @Override
    public void sumData(MultipartFile file, HttpServletResponse response) {
        //读取
        List<ImportData> importData = ExcelUtils.importByExcel(file, ImportData.class);
        //计算
        for (ImportData data : importData) {
            double allScore = data.getEnglish() + data.getLanguage() + data.getMaths();
            double averageScore = allScore / 3;
            data.setAllScore(allScore);
            data.setAverageScore(averageScore);
            log.info(JSON.toJSONString(data));
        }
        //导出
        ExcelUtils.useTemplateExport(response,"template/dataTemplate.xlsx","useTemplate",ImportData.class,importData);
        //ExcelUtils.normalExport(response,"noTemplate",ImportData.class,importData);
    }
}
